<?php
namespace org;
class Excel
{

    public function init()
    {
        require_once dirname(__FILE__) . '/PHPExcel/PHPExcel.php';
    }

    /**
     * 导出
     * @param string $name
     * @param array $cell
     * @param array $data
     * @throws \PHPExcel_Exception
     * @throws \PHPExcel_Reader_Exception
     */
    public static function export($name = '', $cell = [], $data = [])
    {
        self::init();
        $xlsTitle = iconv('utf-8', 'gb2312', $name);
        $fileName = $name . date('_YmdHi');
        $cellNum = count($cell);
        $dataNum = count($data);

        $objPHPExcel = new \PHPExcel();
        $cellName = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:' . $cellName[$cellNum - 1] . '1');
        $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $name . '  Export time:' . date('Y-m-d H:i:s'));
        for ($i = 0; $i < $cellNum; $i++) {
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i] . '2', $cell[$i][1]);
        }
        for ($i = 0; $i < $dataNum; $i++) {
            for ($j = 0; $j < $cellNum; $j++) {
                $objPHPExcel->getActiveSheet(0)->setCellValueExplicit($cellName[$j] . ($i + 3), $data[$i][$cell[$j][0]]);
            }
        }

        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="' . $xlsTitle . '.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");
        $objWriter = \PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        $objWriter->save('php://output');
        exit;
    }


    /**
     * 导入
     * @param string $filename
     * @param int $startRow
     * @param array $cell
     * @param bool $debug
     * @return array
     * @throws \PHPExcel_Reader_Exception
     */
    public static function importExcel($filename = '', $startRow = 0, $cell = [], $debug = false)
    {
        self::init();

        $objReader = \PHPExcel_IOFactory::createReader('Excel5');

        $objPHPExcel = $objReader->load($filename, $encode = 'utf-8');


        $sheets=$objPHPExcel->getSheetNames();

        foreach($sheets as $key=>$mobile_type){

            $sheet = $objPHPExcel->setActiveSheetIndex($key);

            $highestRow = $sheet->getHighestRow();
            for ($i = $startRow; $i <= $highestRow; $i++) {
                foreach ($cell as $index => $item) {
                    if(self::getField($objPHPExcel, $item, $i)!=""){
                        $data[$key][$i]['mobile_type']=$mobile_type;
                        $data[$key][$i][$index] = self::getField($objPHPExcel, $item, $i);
                    }




                }
            }

            $data = array_values($data);




        }


        foreach($data as $k=>$v){
            foreach($v as $key=>$val){

                $gains=floatval($val['purchasing_price']-$val['market_price']);
                $data[$k][$key]['gains']=$gains;
                if($gains<=3){
                    $data[$k][$key]['star']=0;
                }else if($gains>3 && $gains<=50){
                    $data[$k][$key]['star']=1;
                }else if($gains>=51 && $gains<=100){
                    $data[$k][$key]['star']=2;
                }else if($gains>=101){
                    $data[$k][$key]['star']=3;
                }
                $data[$k][$key]['add_time']=input("date");


            }

        }




        if ($debug === true) {
            dump($data);
            exit;
        }
        return $data;
    }

    private function getField($objPHPExcel, $a, $i)
    {
        return (string)$objPHPExcel->getActiveSheet()->getCell($a . $i)->getValue();
    }
}